package com.android.job.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.android.job.model.MyInfo;

public class DBHelper extends SQLiteOpenHelper
{
	/**
	 * 打印日志时日志信息中的标识符
	 */
	//	private static final String TAG = "DBHelper";

	/**
	 * 数据库的名称
	 */
	private static final String DATABASE_NAME = "OaFlow.db";

	/**
	 * 数据库的版本号，默认情况下是1
	 */
	private static final int DATABASE_VERSION = 2;

	/**
	 * 申请单数据表
	 */
	private static final String REQUET_FORM = "table_request";

	/**
	 * 我的签到记录
	 */
	private static final String MY_LOCATION = "my_location";

	/**
	 * 用来操作的数据库的实例
	 */
	SQLiteDatabase db = null;

	/**
	 * 构造函数
	 * @param context    UI环境上下文
	 */
	public DBHelper(Context context)
	{
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db)
	{
		db.execSQL("create table if not exists " + REQUET_FORM
				+ "(id Integer primary key autoincrement,workId text,"
				+ "name text," + "dept text," + "type text," + "begin text,"
				+ "end text," + "days int," + "reason text," + "approve int)");
		db.execSQL("create table if not exists " + MY_LOCATION
				+ "(id Integer primary key autoincrement,location text,"
				+ "name text," + "time text)");

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
	{
		db.execSQL("drop table " + REQUET_FORM);
		db.execSQL("drop table " + MY_LOCATION);
		db.execSQL("create table if not exists " + REQUET_FORM
				+ "(id Integer primary key autoincrement,workId text,"
				+ "name text," + "dept text," + "type text," + "begin text,"
				+ "end text," + "days int," + "reason text," + "approve int)");
		db.execSQL("create table if not exists " + MY_LOCATION
				+ "(id Integer primary key autoincrement,location text,"
				+ "name text," + "time text)");
	}

	/**
	 * 插入数据
	 * 
	 * @param tableName 要插入的表格的名称
	 * @param values 要插入的数据由数据名称和数据值组成的键值对
	 * 
	 */
	public void insert(String tableName, ContentValues values)
	{
		db = this.getWritableDatabase();

		db.beginTransaction();
		try
		{
			db.insert(tableName, null, values);
			db.setTransactionSuccessful();
		}
		catch (Exception e)
		{
		}
		finally
		{
			db.endTransaction();
			close(db);
		}
	}

	/**
	 * 查询所有的位置
	 * @return
	 */
	public Cursor query()
	{
		db = this.getReadableDatabase();
		Cursor cursor = db.query(MY_LOCATION, null, null, null, null, null,
				"id desc");
		return cursor;
	}

	public Cursor query(String workId, int approve)
	{
		db = this.getReadableDatabase();

		Cursor cursor = db.query(REQUET_FORM, null, "workId = ? "
				+ " and approve = " + approve, new String[]
		{ workId }, null, null, null);

		return cursor;
	}

	public Cursor query(int approve)
	{
		db = this.getReadableDatabase();

		Cursor cursor = db.query(REQUET_FORM, null, " approve = " + approve,
				null, null, null, null);

		return cursor;
	}

	public int update(int id, int state)
	{
		db = this.getReadableDatabase();

		//		Cursor cursor = db.update(REQUET_FORM, null, " id = " + id,
		//				null, null, null, null);
		ContentValues cv = new ContentValues();
		cv.put("approve", state);
		int count = db.update(REQUET_FORM, cv, "id = " + id, null);

		return count;
	}

	/**
	 * 对数据进行的查询操作
	 * 
	 * @param tableName 需要操作的表名
	 * @param whereArgs 要查询的数据中提供的条件参数的名称
	 * @param whereArgsValues 要查询的数据中提供的条件参数的值
	 * @param column 控制哪些字段返回结果（传null是返回所有字段的结果集）
	 * @param orderBy 是否对某一字段进行排序（传null不进行排序）
	 * @return 查找的数据集的指针
	 */
	public Cursor query(String tableName, String[] whereArgs,
			String[] whereArgsValues, String[] column, String orderBy)
	{
		Cursor cursor = null;
		db = this.getReadableDatabase();

		db.beginTransaction();
		try
		{
			//当传入的参数为空时，表示操作所有的记录
			if (whereArgs == null)
			{
				cursor = db.query(tableName, column, null, null, null, null,
						orderBy);
			}
			else
			{
				//当传入的参数为一个时
				if (whereArgs.length == 1)
				{
					//当传入的参数和参数值分别为一个时
					if (whereArgsValues.length == 1)
					{
						cursor = db.query(tableName, column, whereArgs[0]
								+ "= ?", whereArgsValues, null, null, orderBy);
					}
					//当传入的参数为一个，参数值为多个时
					else
					{
						cursor = db.query(
								tableName,
								column,
								createSQL(whereArgs, whereArgsValues,
										whereArgsValues.length), null, null,
								null, orderBy);
					}
				}
				//当传入的参数和参数值分别为多个时，并且参数和参数值是一一对应的
				else
				{
					cursor = db.query(
							tableName,
							column,
							createSQL(whereArgs, whereArgsValues,
									whereArgs.length), null, null, null,
							orderBy);
				}
			}
			db.setTransactionSuccessful();
		}
		catch (Exception e)
		{
		}
		finally
		{
			db.endTransaction();

		}
		return cursor;
	}

	/**
	 * 将用户提供的参数拼接成一条完整的删除数据库的SQL语句
	 * 
	 * @param tableName 要操作的表的名称
	 * @param whereArgs 删除数据的条件参数
	 * @param whereArgsValues 删除数据的条件参数的值
	 * @param num 提供的条件中参数的个数
	 * @return 拼接完成后的删除SQL语句
	 */
	private String createSQL(String[] whereArgs, String[] whereArgsValues,
			int length)
	{
		StringBuffer sql = new StringBuffer(" ");
		//当传入的参数为一个时
		if (whereArgs.length == 1)
		{
			for (int i = 0; i < length; i++)
			{
				sql.append(whereArgs[0] + " = '" + whereArgsValues[i] + "'");
				if (i < length - 1)
				{
					sql.append(" or ");
				}
			}
		}
		//当传入的参数和参数值分别为多个时，并且参数和参数值是一一对应的
		else
		{
			for (int i = 0; i < length; i++)
			{
				sql.append(whereArgs[i] + " = '" + whereArgsValues[i] + "'");
				if (i < length - 1)
				{
					sql.append(" and ");
				}
			}
		}
		return sql.toString();
	}

	/**
	 * 关闭当前正在使用的数据库
	 * @param db 正处于打开状态的数据库   
	 */
	public void close(SQLiteDatabase db)
	{
		try
		{
			if (db != null)
			{
				if (db.isOpen())
				{
					db.close();
					db = null;
				}
			}
		}
		catch (Exception e)
		{
		}
		finally
		{
			if (db != null)
			{
				db.close();
			}
		}
	}

	/**
	 * 关闭所有链接中的数据库
	 */
	public void closeDatabase()
	{
		try
		{
			close();
		}
		catch (Exception e)
		{
		}
	}

}
